# 业务数据库 -> ODS/DIM -> DWD -> DWM -> DWS -> MySQL -> BI
# 这个脚本将MySQL数据导入到ODS、DIM、数据采集

HIVE_HOME="/usr/bin/hive"

# 一：将MySQL数据导入ODS层
# 1.1 导入数据到customer_relationship_tmp临时表中
sqoop import \
--connect jdbc:mysql://192.168.10.150:3306/scrm \
--username root \
--password 123456 \
--query 'select id, create_date_time, update_date_time, deleted, customer_id, first_id, belonger, belonger_name, initial_belonger, distribution_handler, business_scrm_department_id, last_visit_time, next_visit_time, origin_type, itcast_school_id, itcast_subject_id, intention_study_type, anticipat_signup_date, level, creator, current_creator, creator_name, origin_channel, comment, first_customer_clue_id, last_customer_clue_id, process_state, process_time, payment_state, payment_time, signup_state, signup_time, notice_state, notice_time, lock_state, lock_time, itcast_clazz_id, itcast_clazz_time, payment_url, payment_url_time, ems_student_id, delete_reason, deleter, deleter_name, delete_time, course_id, course_name, delete_comment, close_state, close_time, appeal_id, tenant, total_fee, belonged, belonged_time, belonger_time, transfer, transfer_time, follow_type, transfer_bxg_oa_account, transfer_bxg_belonger_name, date_format("9999-12-31","%Y-%m-%d") as end_time, current_date() as start_time from customer_relationship where $CONDITIONS' \
--hcatalog-database itcast_ods \
--hcatalog-table customer_relationship_tmp \
-m 10 \
--split-by id

# 1.1.1 将数据覆盖插入到customer_relationship表中
$HIVE_HOME -e "
--分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;
--分桶
set hive.enforce.bucketing=true;
set hive.enforce.sorting=true;
set hive.optimize.bucketmapjoin = true;
set hive.auto.convert.sortmerge.join=true;
set hive.auto.convert.sortmerge.join.noconditionaltask=true;
--插入到原表中
insert overwrite table itcast_ods.customer_relationship partition(start_time)
select * from itcast_ods.customer_relationship_tmp;
--删除临时表
drop table itcast_ods.customer_relationship_tmp;
"

# 1.2 导入数据到customer_clue_tmp临时表中
sqoop import \
--connect jdbc:mysql://192.168.10.150:3306/scrm \
--username root \
--password 123456 \
--query 'select id,create_date_time,update_date_time,deleted,customer_id,customer_relationship_id,session_id,sid,status,user as users,create_time,platform,s_name,seo_source,seo_keywords,ip,referrer,from_url,landing_page_url,url_title,to_peer,manual_time,begin_time,reply_msg_count,total_msg_count,msg_count,comment,finish_reason,finish_user,end_time,platform_description,browser_name,os_info,area,country,province,city,creator,name,"-1" as idcard,"-1" as phone,itcast_school_id,itcast_school,itcast_subject_id,itcast_subject,"-1" as wechat,"-1" as qq,"-1" as email,gender,level,origin_type,information_way,working_years,technical_directions,customer_state,valid,anticipat_signup_date,clue_state,scrm_department_id,superior_url,superior_source,landing_url,landing_source,info_url,info_source,origin_channel,course_id,course_name,zhuge_session_id,is_repeat,tenant,activity_id,activity_name,follow_type,shunt_mode_id,shunt_employee_group_id,date_format("9999-12-31","%Y-%m-%d") as ends_time,current_date() as starts_time from customer_clue where $CONDITIONS' \
--hcatalog-database itcast_ods \
--hcatalog-table customer_clue_tmp \
-m 10 \
--split-by id

# 1.2.1 将数据覆盖插入到customer_clue表中
$HIVE_HOME -e "
--分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;
--分桶
set hive.enforce.bucketing=true;
set hive.enforce.sorting=true;
set hive.optimize.bucketmapjoin = true;
set hive.auto.convert.sortmerge.join=true;
set hive.auto.convert.sortmerge.join.noconditionaltask=true;
--插入到原表中
insert overwrite table itcast_ods.customer_clue partition(starts_time)
select * from itcast_ods.customer_clue_tmp;
--删除临时表
drop table itcast_ods.customer_clue_tmp;
"

# 二：将MySQL数据导入DIM层
# 2.1 导入数据到customer客户表中
sqoop import \
--connect jdbc:mysql://192.168.10.150:3306/scrm \
--username root \
--password 123456 \
--query 'select id, customer_relationship_id, create_date_time, update_date_time, deleted, name, idcard, birth_year, gender, phone,  wechat, qq, email, area, leave_school_date, graduation_date, bxg_student_id, creator, origin_type, origin_channel, tenant, md_id, current_date() as start_time from customer where $CONDITIONS' \
--hcatalog-database itcast_dimen \
--hcatalog-table customer \
-m 10 \
--split-by id

# 2.2 导入数据到employee员工表中
sqoop import \
--connect jdbc:mysql://192.168.10.150:3306/scrm \
--username root \
--password 123456 \
--query 'select id,email,real_name,-1 as phone,department_id,department_name,remote_login,job_number,cross_school,last_login_date,creator,create_date_time,update_date_time,deleted,scrm_department_id,leave_office,leave_office_time,reinstated_time,superior_leaders_id,tdepart_id,tenant,ems_user_name, current_date() as start_time from employee where $CONDITIONS' \
--hcatalog-database itcast_dimen \
--hcatalog-table employee \
-m 10 \
--split-by id

# 2.3 导入数据到scrm_department部门表中
sqoop import \
--connect jdbc:mysql://192.168.10.150:3306/scrm \
--username root \
--password 123456 \
--query 'select *,current_date() as start_time from scrm_department where $CONDITIONS' \
--hcatalog-database itcast_dimen \
--hcatalog-table scrm_department \
-m 10 \
--split-by id

# 2.4 导入数据到itcast_school学校表中
sqoop import \
--connect jdbc:mysql://192.168.10.150:3306/scrm \
--username root \
--password 123456 \
--query 'select *, current_date() as start_time from itcast_school where $CONDITIONS' \
--hcatalog-database itcast_dimen \
--hcatalog-table itcast_school \
-m 10 \
--split-by id

# 2.5 导入数据到itcast_subject学科表中
sqoop import \
--connect jdbc:mysql://192.168.10.150:3306/scrm \
--username root \
--password 123456 \
--query 'select *, current_date() as start_time from itcast_subject where $CONDITIONS' \
--hcatalog-database itcast_dimen \
--hcatalog-table itcast_subject \
-m 10 \
--split-by id
